Skip to main content

06. 数据库设计原理与实践

1. 数据依赖与关系模式的范式化​

1.1 数据依赖 (Data Dependency)​

数据依赖描述了关系中属性之间存在的约束关系，是数据库设计中理解和消除数据冗余的基础。

1.1.1 函数依赖 (Function Dependency, FD)​

  • 定义：关系中一个或一组属性的值可以唯一确定另一个或一组属性的值。
  • 表示：X→YX \to Y，表示属性集 XX 函数决定属性集 YY。
  • 重要性：FD 是最基本、最重要的数据库设计依赖类型。
  • 用例：
    • 在学生表 Student(学号, 姓名, 性别, 班级) 中，学号 可以唯一确定 姓名、性别 和 班级，即 学号 -> 姓名, 性别, 班级。
    • 在课程表 Course(课程号, 课程名, 学分) 中，课程号 可以唯一确定 课程名 和 学分，即 课程号 -> 课程名, 学分。

1.1.2 多值依赖 (Multi-valued Dependency, MVD)​

  • 定义：关系中一个属性的值可以决定另一组属性的一组值，而不是单个值。
  • 表示：X↠YX \twoheadrightarrow Y，表示属性集 XX 多值决定属性集 YY。
  • 用例：假设有一个关系 Project(项目号, 员工号, 技能)，一个项目可以有多个员工，一个员工可以有多种技能。如果 项目号 确定了 员工号 的一组值，并且 项目号 也确定了 技能 的一组值，那么可能存在多值依赖。例如，项目号 决定了参与该项目的所有员工，也决定了该项目所需的所有技能，且员工和技能之间没有直接关联。

1.1.3 连接依赖 (Join Dependency, JD)​

  • 定义：一个关系可以无损连接分解成多个子关系，这些子关系的自然连接等于原关系。
  • 重要性：JD 是无损连接分解的约束条件，确保分解后的信息不丢失。

1.2 关系模式的范式 (Normal Forms)​

范式是衡量关系模式规范化程度的标准，旨在消除数据冗余和更新异常。

1.2.1 第一范式 (First Normal Form, 1NF)​

  • 定义：关系中的每个属性都必须是原子性的 (atomic)，即不可再分。
  • 判断：检查每个属性的值是否都是单一的、不可再分的。
  • 用例：

    • 非 1NF 示例：

      姓名部门地址
      张三销售北京市朝阳区望京SOHO
      李四研发上海市浦东新区陆家嘴

    • 问题：地址 属性包含 省、市、街道 等多个信息，不是原子性的。

    • 转换为 1NF：

      姓名部门省市街道
      张三销售北京朝阳区望京SOHO
      李四研发上海浦东新区陆家嘴

1.2.2 第二范式 (Second Normal Form, 2NF)​

  • 定义：关系处于 1NF，并且不存在非主属性对码的部分函数依赖 (partially function dependency)。
    • 部分函数依赖：指非主属性只依赖于码的一部分，而不是码的全部。
  • 判断：
    1. 关系是否满足 1NF。
    2. 找出所有的码（候选码）。
    3. 检查是否存在非主属性只依赖于码的真子集。
  • 非 2NF 的问题 (更新异常)：
    • 插入异常 (Insert Abnormity)：无法在不插入相关联数据的情况下插入新数据。
      • 用例：如果学生 S# 尚未选课，无法插入其基本信息。
    • 删除异常 (Delete Abnormity)：删除某条记录时，意外丢失其他重要信息。
      • 用例：如果一个学生退选了所有课程，其基本信息（姓名、年龄、地址）也会随之丢失。
    • 更新异常 (Update Abnormity)：由于数据冗余，更新时需要修改多处，容易导致数据不一致。
      • 用例：如果一个学生的姓名发生变化，需要更新所有包含该学生选课记录的行。
  • 用例：
    • 非 2NF 示例：S(S#, SNAME, AGE, ADDR, C#, GRADE)
      • 码：(S#, C#)
      • 函数依赖：
        • (S#, C#) -> GRADE
        • S# -> SNAME, AGE, ADDR (非主属性 SNAME, AGE, ADDR 部分依赖于码 (S#, C#) 的一部分 S#)
    • 转换为 2NF (分解)：遵循“一事一地”原则，将关系分解为：
      • S_INFO(S#, SNAME, AGE, ADDR)
      • SC_GRADE(S#, C#, GRADE)

1.2.3 第三范式 (Third Normal Form, 3NF)​

  • 定义：关系处于 2NF，并且不存在非主属性对码的传递函数依赖 (transfer function dependency)。
    • 传递函数依赖：指 X→YX \to Y, Y→ZY \to Z，且 YY 不是码的任何部分， ZZ 不是主属性。
  • 判断：
    1. 关系是否满足 2NF。
    2. 检查是否存在非主属性通过另一个非主属性间接依赖于码。
  • 非 3NF 的问题 (更新异常)：与非 2NF 类似，也会导致插入、删除、更新异常。
    • 用例：
      • 插入异常：在员工的工资级别确定之前，无法输入工资级别与工资的对应关系。
      • 删除异常：如果某个工资级别只有一个员工，当该员工被删除时，该工资级别与工资的对应关系也会丢失。
      • 更新异常：如果某个工资级别的工资发生变化，需要更新所有该级别员工的记录。
  • 用例：
    • 非 3NF 示例：EMP(EMP#, SAL_LEVEL, SALARY)
      • 码：EMP#
      • 函数依赖：
        • EMP# -> SAL_LEVEL
        • SAL_LEVEL -> SALARY (非主属性 SALARY 传递依赖于码 EMP# 经过 SAL_LEVEL)
    • 转换为 3NF (分解)：遵循“一事一地”原则，将关系分解为：
      • EMP_INFO(EMP#, SAL_LEVEL)
      • SAL_INFO(SAL_LEVEL, SALARY)

1.3 范式化总结与权衡​

  • 目标：通过范式化，消除数据冗余，减少更新异常，提高数据一致性。
  • “一事一地”原则：每项信息只存储在一个地方，避免重复。
  • 局限性：仅仅达到 3NF (或 BCNF) 在结构上可能仍有不足，需要结合实际业务和性能需求进行权衡。
  • 权衡：在结构规范化、减少数据冗余和提高数据库访问性能之间进行仔细权衡和适当折中。过度范式化可能导致查询时需要进行更多的连接操作，从而降低查询性能。

2. 数据库设计方法与流程​

2.1 数据库设计方法​

2.1.1 过程驱动方法 (Procedure Oriented Method)​

  • 特点：以业务流程为中心，直接根据业务凭证、报表等设计数据库模式。
  • 优点：项目初期设计速度快。
  • 缺点：
    • 缺乏对数据及其内部关系的详细分析。
    • 难以保证软件质量。
    • 难以适应未来需求和环境变化。
  • 适用场景：不适用于大型、复杂系统的开发。

2.1.2 数据驱动方法 (Data Oriented Method)​

  • 特点：以数据为中心，基于对数据及其内部关系的详细分析来设计数据库模式。
  • 优点：
    • 能满足当前需求，并兼顾潜在需求。
    • 易于适应未来需求和环境变化。
  • 适用场景：推荐用于大型、复杂系统的开发。

2.2 数据库设计流程 (Database Design Flow)​

数据库设计是一个多阶段、迭代的过程，通常包括以下步骤：

2.2.1 需求分析 (Requirement Analysis)​

  • 目标：全面、准确地收集和分析用户需求，包括信息需求和处理需求。
  • 核心产物：数据字典 (Data Dictionary) 和数据流图 (Data Flow Diagram, DFD) 或 UML 图。
  • 关键任务：
    • 识别冲突：
      • 命名冲突 (Name Conflicts)：
        • 同义词 (Synonym)：不同名称表示相同含义（例如：客户编号 和 顾客ID）。
        • 同名异义 (Homonym)：相同名称表示不同含义（例如：数量 在库存中指库存量，在订单中指订购量）。
      • 概念冲突 (Concept Conflicts)：对同一概念的理解不同。
      • 域冲突 (Domain Conflicts)：属性取值范围或类型不一致。
    • 编码规范：
      • 信息标准化。
      • 识别实体。
      • 信息压缩。
    • 信息溯源：确保所有信息具有唯一的来源和唯一的责任方。

2.2.2 概念设计 (Concept Design)​

  • 目标：根据需求分析结果，抽象出独立于具体 DBMS 的概念模型。
  • 核心产物：实体-关系 (Entity-Relationship, ER) 模型及其 ER 图。
  • 关键任务：
    • 识别实体 (Identify Entities)：从数据字典中识别出重要的、独立的数据对象。
    • 定义实体间的关系 (Define the Relationships between Entities)：确定实体之间的一对一、一对多、多对多等关系。
    • 绘制 ER 图并与用户讨论 (Draw ER Diagram and Discuss it with User)：使用 ERWin、Rose 等工具绘制 ER 图，并与用户确认模型的正确性和完整性。
  • ER 模型图例：
    • 实体 (Entity)：矩形
    • 关系 (Relation)：菱形
    • 属性 (Attribute)：椭圆形
    • 用例：
      • 学生 (实体) -- S#, SNAME (属性)
      • 课程 (实体) -- C#, CNAME (属性)
      • 选课 (关系) -- GRADE (属性)，连接学生和课程实体

2.2.3 逻辑设计 (Logic Design)​

  • 目标：将概念模型转换为特定 DBMS 支持的数据模型（如关系模型），并进行规范化。
  • 核心产物：数据库模式（表、视图定义）。
  • 基本标准：达到 3NF (或更高范式，如 BCNF)。
  • 关键任务：
    • 将 ER 图中的实体和关系转换为表 (Translate entities and relationships in ER diagram to tables)。
    • 表和属性的命名规则 (Naming rule of table and attribute)。
    • 定义每个属性的类型和域 (Define the type and domain of every attribute)。
    • 适当的反规范化 (Suitable Denormalization)：在满足性能需求时，可以适当降低范式等级，引入少量冗余以减少连接操作。
    • 必要的视图 (Necessary View)：为不同用户或应用提供定制的数据视图。
    • 考虑遗留系统中的表 (Consider the tables in legacy system)。
    • 接口表 (Interface Tables)：用于与其他系统进行数据交换。

2.2.4 物理设计 (Physical Design)​

  • 目标：根据特定 DBMS 的特性、硬件和操作系统环境，优化数据库的物理存储结构和存取路径，以提高性能。
  • 核心任务：
    • 创建必要的索引 (Creating necessary indexes)：
      • 单属性索引 (Single attribute indexes)
      • 多属性索引 (Multi attributes indexes)
      • 聚簇索引 (Cluster indexes)
      • 原则：经常作为查询条件的属性应建立索引。
    • 其他问题：
      • 分区设计 (Partition design)：将大表分解为更小的、可管理的部分。
      • 存储过程 (Stored procedure)：预编译的 SQL 语句集合，提高执行效率和安全性。
      • 触发器 (Trigger)：在特定数据库事件发生时自动执行的 SQL 代码。
      • 完整性约束 (Integrity constraints)：定义数据的一致性规则（如主键、外键、唯一性约束、检查约束）。

3. 数据库设计要点与权衡​

  • 结构规范化不足：仅仅在结构上达到 3NF (BCNF) 是不够的，还需要结合实际业务需求和性能考量。
  • “一事一地”的深层理解：不仅指每项信息的唯一存储，更要提取出问题的本质，识别出本质上同一概念的信息项。
  • 模式相似表的合并：对于表达类似信息、模式相似但取值不同的表，应尽量合并，例如学习经历和进修经历，奖励信息和惩处信息。
  • 基于效率和用途的拆分：考虑到效率、用途等因素，该分开的表仍应分开，例如本科生基本信息和研究生基本信息。
  • 索引和文件结构优化：结合 DBMS 内部实现技术，合理设计索引和文件结构，为查询优化准备好存取路径。
  • 规范化与性能的权衡：在结构规范化、减少数据冗余和提高数据库访问性能之间进行仔细权衡，适当折中。这是数据库设计中一个重要的考点和实践难点。
  • 数据库设计实例分析：通过具体的案例分析，将理论知识应用于实践，理解设计过程中的决策和取舍。